<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\Cities;
use App\Models\FileUpload;
use App\Models\FilmTypeMaster;
use App\Models\MacroManagement;
use App\Models\MakeMaster;
use App\Models\ModelMaster;
use App\Models\PartMaster;
use App\Models\ProvinceMaster;
use App\Models\RegionMaster;
use App\Models\Roles;
use App\Models\Permissions;
use App\Models\RollMaster;
use App\Models\RollRestlen;
use Illuminate\Http\Request;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;

/*
 * 卷膜管理*/
class OldWarrantyController extends Controller
{
    /*
     * 膜卷excel导入
     * 膜卷批量导入
     * 第一行为表头不录入*/
    public function import(Request $request)
    {
        checkAuthority();
        $page = [
            'page_name' => 'old-warranty',
            'title' => '旧质保系统',
        ];

        return view('admin.old-warranty.import', ['page'=>$page]);
    }

    /*
     * 第一行为表头不录入*/
    public function handle_import(Request $request)
    {
        $page = [
            'page_name' => 'old-warranty',
            'title' => '旧质保系统',
        ];
        $user_id = session('admin.id');
        $user_type = session('admin.user_type');
        if ($user_type != 1) {
            return abort(404);
        }
        $file = $request->file('file');
        if (empty($file)) {
            return redirect()->back()->withInput()->with('trash', ['content'=>'文件不存在！', 'type'=>'error']);
        }
        if ($file->getClientOriginalExtension() != 'xlsx') {
            return redirect()->back()->withInput()->with('trash', ['content'=>'仅支持上传xlsx类型文件', 'type'=>'error']);
        }

        $filename = Str::random(16) . '.xlsx';
        $path = getFileStorePath(2);
        $bool = $file->storeAs($path['path'], $filename, 'storage');
        $full_file_name = $path['path'].$filename; // /upload/XXX/filename
        $mod = new FileUpload();
        $mod->type = 2;
        $mod->name = '旧质保文件上传';
        $mod->user_id = $user_id;
        $mod->file = $full_file_name;
        $mod->save();

        $region_master = DB::table('region_master')->get();
        $province_master = DB::table('province_master')->get();
        $city_master = DB::table('cities')->get();
        $region_en = $region_zh_tw = $region_zh = [];
        $province_en = $province_zh_tw = $province_zh = [];
        $city_en = $city_zh_tw = $city_zh = [];
        foreach ($region_master as $k => $v) {
            $region_en[$v->english_value] = $v->id;
            $region_zh_tw[$v->traditional_chiness_value] = $v->id;
            $region_zh[$v->simplified_chiness_value] = $v->id;
        }
        foreach ($province_master as $k => $v) {
            $province_en[$v->english_value] = $v->id;
            $province_zh_tw[$v->traditional_chiness_value] = $v->id;
            $province_zh[$v->simplified_chiness_value] = $v->id;
        }
        foreach ($city_master as $k => $v) {
            $city_en[$v->english_value] = $v->id;
            $city_zh_tw[$v->traditional_chiness_value] = $v->id;
            $city_zh[$v->simplified_chiness_value] = $v->id;
        }
        // 装贴门店表
        $user_master = DB::connection('oldWarranty')->table('users')->get(['user_id', 'company_name', 'status']);
        $users = [];
        foreach ($user_master as $k => $v) {
            if ($v->status == 1) {
                if (strpos($v->user_id, '-') === false) {
                    $key = trim($v->company_name);
                    $users[$key] = $v->user_id;
                }
            }
        }
        foreach ($user_master as $k => $v) {
            if ($v->status == 1) {
                $key = trim($v->company_name);
                if (empty($users[$key])) {
                    $users[$key] = $v->user_id;
                }
            }
        }
        foreach ($user_master as $k => $v) {
            $key = trim($v->company_name);
            if (empty($users[$key])) {
                $users[$key] = $v->user_id;
            }
        }
        // 安装部位
        $part = DB::connection('oldWarranty')->table('part_master')->get();
        $parts = [];
        foreach ($part as $k =>$v) {
            $parts[$v->part_name] = $v->part_id;
        }
        // 膜的种类
        $film = DB::connection('oldWarranty')->table('film_type_master')->get();
        $films = [];
        foreach ($film as $k =>$v) {
            $films[$v->film_type_name] = $v->film_type_id;
        }
        // 品牌
        $make = DB::connection('oldWarranty')->table('make_master')->get();
        $makes = [];
        foreach ($make as $k =>$v) {
            $key = $v->year_id.'#'.$v->make_name;
            $makes[$key] = $v->make_id;
        }

        $spreadsheet = IOFactory::load($path['base'] . $filename);
        $raw = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        $arrays = [];
        foreach ($raw as $k => $v) {
            if ($v['A'] == '姓名') {
                continue;
            }
            if (empty($v['A'])) {
                continue;
            }
            $arrays[] = $v;
        }

        $region = Arr::pluck($arrays, 'B');
        $province = Arr::pluck($arrays, 'C');
        $city = Arr::pluck($arrays, 'D');
        $region = array_unique($region);
        $province = array_unique($province);
        $city = array_unique($city);
        $regionIs = [];
        foreach ($region as $k => $v) {
            if (empty($v)) {
                $v = '中国大陆';
            } else {
                if ($v == '中国') {
                    $v = '中国大陆';
                }
                if ($v == '台湾') {
                    $v = '台湾地区';
                }
                if ($v == '澳门') {
                    $v = '澳门特别行政区';
                }
                if ($v == '香港') {
                    $v = '香港特别行政区';
                }
            }
            $region[$k] = $v;
        }

        $errors = [];
        $regions = [];
        $region_error = [];
        foreach ($region as $k => $v) {
            if (!empty($region_en[$v])) {
                $regions[$v] = $region_en[$v];
            } elseif (!empty($region_zh_tw[$v])) {
                $regions[$v] = $region_zh_tw[$v];
            } elseif (!empty($region_zh[$v])) {
                $regions[$v] = $region_zh[$v];
            } else {
                $region_error[] = $v;
            }
        }
        if (!empty($region_error)) {
            $errors[] = '以下地区不存在：'.implode('，', $region_error);
        }
        foreach ($province as $k => $v) {
            if (mb_strpos ($v, '宁夏') === 0) {
                $v = '宁夏';
            }
            if (mb_strpos ($v, '内蒙古') === 0) {
                $v = '内蒙古';
            }
            if (mb_strpos ($v, '西藏') === 0) {
                $v = '西藏';
            }
            if (mb_strpos ($v, '新疆') === 0) {
                $v = '新疆';
            }
            if (mb_strpos ($v, '香港') === 0) {
                $v = '香港';
            }
            if (mb_strpos ($v, '澳门') === 0) {
                $v = '澳门';
            }
            if (mb_strpos ($v, '台湾') === 0) {
                $v = '台湾';
            }
            $province[$k] = $v;
        }
        $provinces = [];
        $province_error = [];
        foreach ($province as $k => $v) {
            $v1 = $v;
            if (mb_strpos ($v, '宁夏') === 0) {
                $v1 = '宁夏';
            }
            if (mb_strpos ($v, '内蒙古') === 0) {
                $v1 = '内蒙古';
            }
            if (mb_strpos ($v, '西藏') === 0) {
                $v1 = '西藏';
            }
            if (mb_strpos ($v, '新疆') === 0) {
                $v1 = '新疆';
            }
            if (mb_strpos ($v, '香港') === 0) {
                $v1 = '香港';
            }
            if (mb_strpos ($v, '澳门') === 0) {
                $v1 = '澳门';
            }
            if (mb_strpos ($v, '台湾') === 0) {
                $v1 = '台湾';
            }
            $v2 = $v.'省';
            $v3 = $v.'市';
            if (!empty($province_en[$v])) {
                $provinces[$v] = $province_en[$v];
            } elseif (!empty($province_zh_tw[$v])) {
                $provinces[$v] = $province_zh_tw[$v];
            } elseif (!empty($province_zh[$v])) {
                $provinces[$v] = $province_zh[$v];
            } elseif (!empty($province_en[$v1])) {
                $provinces[$v] = $province_en[$v1];
            } elseif (!empty($province_zh_tw[$v1])) {
                $provinces[$v] = $province_zh_tw[$v1];
            } elseif (!empty($province_zh[$v1])) {
                $provinces[$v] = $province_zh[$v1];
            } elseif (!empty($province_en[$v2])) {
                $provinces[$v] = $province_en[$v2];
            } elseif (!empty($province_zh_tw[$v2])) {
                $provinces[$v] = $province_zh_tw[$v2];
            } elseif (!empty($province_zh[$v2])) {
                $provinces[$v] = $province_zh[$v2];
            } elseif (!empty($province_en[$v3])) {
                $provinces[$v] = $province_en[$v3];
            } elseif (!empty($province_zh_tw[$v3])) {
                $provinces[$v] = $province_zh_tw[$v3];
            } elseif (!empty($province_zh[$v3])) {
                $provinces[$v] = $province_zh[$v3];
            } else {
                $province_error[] = $v;
            }
        }
        if (!empty($province_error)) {
            $errors[] = '以下省不存在：'.implode('，', $province_error);
        }
        $citys = [];
        $city_error = [];
        foreach ($city as $k => $v) {
            $v3 = $v.'市';
            if (!empty($city_en[$v])) {
                $citys[$v] = $city_en[$v];
            } elseif (!empty($city_zh_tw[$v])) {
                $citys[$v] = $city_zh_tw[$v];
            } elseif (!empty($city_zh[$v])) {
                $citys[$v] = $city_zh[$v];
            } elseif (!empty($city_en[$v3])) {
                $citys[$v] = $city_en[$v3];
            } elseif (!empty($city_zh_tw[$v3])) {
                $citys[$v] = $city_zh_tw[$v3];
            } elseif (!empty($city_zh[$v3])) {
                $citys[$v] = $city_zh[$v3];
            } else {
                $city_error[] = $v;
            }
        }
        if (!empty($city_error)) {
            $errors[] = '以下城市不存在：'.implode('，', $city_error);
        }
        $errors = [];
        $uploads = [];
        $wvcs = [];
        $name_error = [];
        $region_error = [];
        $city_error = [];
        $province_error = [];
        $phone_error = [];
        $address_error = [];

        $make_error = [];
        $user_error = [];
        $time_error = [];
        $part_error = [];
        $film_error = [];
        $time = Arr::pluck($arrays, 'P');
        $ts = [];
        $es = [];
        foreach ($time as $v) {

            $C11 = trim($v);
            $installation_date = strtotime($C11);
            $es[] = $installation_date;
            $installation_date = date('Y-m-d', $installation_date);
            $ts[] = $installation_date;
        }
        foreach ($arrays as $k => $v) {
            $mkey = $v['I'].'#'.$v['J'];
            $make_id = 0;
            $user_id = 0;
            if (empty($makes[$mkey])) {
                $make_error[] = $mkey;
            } else {
                $make_id = $makes[$mkey];
            }
            $un = trim($v['N']);
            if (empty($users[$un])) {
                $user_error[] = $un;
            } else {
                $user_id = $users[$un];
            }
            $C11 = $v['P'];
            $installation_date = '';
            try {
                if (is_string($C11)) {
                    $C11 = trim($C11);
                    if (mb_strpos($C11, '年')) {
                        $idate_arr = date_parse_from_format('Y年m月d日',$C11);
                        $installation_date = mktime(0,0,0,$idate_arr['month'],$idate_arr['day'],$idate_arr['year']);
                        $installation_date = date('Y-m-d', $installation_date);
                    } else {
                        $installation_date = strtotime($C11);
                        $installation_date = date('Y-m-d', $installation_date);
                    }
                } else {
                    $installation_date = Date::excelToTimestamp($C11);
                    $installation_date = date('Y-m-d', $installation_date);
                }
            } catch (\Exception $exception) {
                $time_error[] = $v['P'];
            }

            $precut_or_manual = $v['S'] == '手工' ? 2 : 1;
            $roll_number = $v['T'];
            $length = (int) $v['U'];
            $width = (int) $v['W'];
            $additional_comments = $v['X'];
            $part_id = $film_type_id = 0;
            $par = trim($v['R']);
            if (!empty($parts[$par])) {
                $part_id = $parts[$par];
            } else {
                $part_error[] = $par;
            }
            $fil = trim($v['V']);
            if (!empty($films[$fil])) {
                $film_type_id = $films[$fil];
            } else {
                $film_error[] = $fil;
            }
            $rng = trim($v['B']);
            if (empty($rng)) {
                $rng = '中国大陆';
            }
            $region_id = 0;
            $province_id = 0;
            $city = 0;
            if (empty($regions[$rng])) {
                $region_error[] = $v['B'];
            } else {
                $region_id = $regions[$rng];
            }
            $pnm = trim($v['C']);
            if (empty($provinces[$pnm])) {
                $province_error[] = $v['C'];
            } else {
                $province_id = $provinces[$pnm];
            }
            $cnm = trim($v['D']);
            if (empty($citys[$cnm])) {
                $city_error[] = $v['D'];
            } else {
                $city = $citys[$cnm];
            }
            $year = (int) trim($v['I']);

            $item = [
                'first_name' => $v['A'],
                'region_id' => $region_id,
                'province_id' => $province_id,
                'city' => $city,
                'zip' => (int) trim($v['E']),
                'email_address' => trim($v['F']),
                'phone_number' => trim($v['G']),
                'address' => trim($v['H']),
                'year_id' => $year,
                'make_id' => $make_id,
                'model_id' => trim($v['K']),
                'license_plate' => trim($v['L']),
                'vin_number' => trim($v['M']),
                'user_id' => $user_id,
                'installation_price' => trim($v['Q']),
                'installation_date' => $installation_date,
                'installer_name' => trim($v['O']),
                'additional_comments' => trim($v['X']),
                'last_name' => '',
                'image_front' => '',
                'image_back' => '',
                'image_driver_side' => '',
                'image_passenger_side' => '',
                'sub_model_id' => 0,
                'vehicle_coverage_id' => 0,
                'pre_id' => 0,
                'language_id' => 0,
                'extension' => '+86',
            ];
            $coverage = [
                'part_id' => $part_id,
                'precut_or_manual' => $precut_or_manual,
                'roll_number' => $roll_number,
                'film_type_id' => $film_type_id,
                'width' => $width,
                'length' => $length,
            ];
            $uploads[] = [
                'warranty' => $item,
                'coverage' => $coverage,
            ];
        }
        if (!empty($region_error)) {
            $errors[] = '以下地区不存在：'.implode('，', $region_error);
        }
        if (!empty($province_error)) {
            $errors[] = '以下省不存在：'.implode('，', $province_error);
        }
        if (!empty($city_error)) {
            $errors[] = '以下城市不存在：'.implode('，', $city_error);
        }
        if (!empty($male_error)) {
            $errors[] = '以下年份内车辆品牌不存在：'.implode('，', $male_error);
        }
        if (!empty($user_error)) {
            $errors[] = '以下转贴门店不存在：'.implode('，', $user_error);
        }
        if (!empty($time_error)) {
            $errors[] = '以下安装日期无法正常转换：'.implode('，', $time_error);
        }
        if (!empty($part_error)) {
            $errors[] = '以下安装部位不存在：'.implode('，', $part_error);
        }
        if (!empty($film_error)) {
            $errors[] = '以下膜的种类不存在：'.implode('，', $film_error);
        }
        // return [$uploads];
        $datas = [];
        if (empty($errors)) {
            $state = true;
            DB::beginTransaction();
            foreach ($uploads as $k => $v) {
                if (!$state) {
                    continue;
                }
                $id = DB::connection('oldWarranty')->table('warranty')->insertGetId($v['warranty']);
                if (empty($id)) {
                    DB::rollBack();
                    $state = false;
                    $errors[] = '数据库插入失败，自动回滚；';
                }
                $v['warranty']['id'] = $id;
                $wvc= $v['coverage'];
                $wvc['warranty_id'] = $id;
                $wid = DB::connection('oldWarranty')->table('warranty_to_vehicle_coverage')->insertGetId($wvc);
                if (empty($wid)) {
                    DB::rollBack();
                    $state = false;
                    $errors[] = '数据库插入失败，自动回滚；';
                }
                $uploads[$k] = [
                    'warranty' => $v['warranty'],
                    'coverage' => $wvc,
                ];
            }
            if ($state) {
                DB::commit();
            }
        }
        return view('admin.old-warranty.import-result', [
            'data'=>$uploads,
            'upload_error'=>$errors,
            'page' => $page,
            'users' => array_flip($users),
        ])->with('trash', '导入成功！');
    }


}
